# -*- coding: utf-8 -*-
# !/usr/bin/env python
# !/usr/bin/python3
# @Time    : 2021/3/13 21:10
# @Author  : buke-freedom
# @File    : read_excel.py
"""
# @description：

"""

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, colors, Alignment

wb = load_workbook("名单.xlsx")

print(wb.sheetnames)
# print(wb.get_sheet_names())
print(wb["Sheet1"])

sheet = wb["Sheet1"]
print(sheet["B5"])
print(sheet["B5"].value)

for cell in sheet["B5:B10"]:
    print(cell[0].value)
# 遍历整个sheet
for row in sheet:
    # print(row)
    # 遍历每一行
    for cell1 in row:
        print(cell1.value, end=",")
    print()

print("-----------------------------------")
# 按指定行进行遍历
#           对应函数          开始行      结束行      结束列
for row in sheet.iter_rows(min_row=4, max_row=12, max_col=3):
    for cell2 in row:
        print(cell2.value, end=",")
    print()
print("-----------------------------------")
# 按列循环遍历
for col in sheet.columns:
    # print(col)
    for cell3 in col:
        print(cell3.value, end=",")
    print()
print("-----------------------------------")
# 按指定列循环遍历
#           对应函数         开始列    结束列      开始行     结束行
for col in sheet.iter_cols(min_col=2, max_col=3, min_row=4, max_row=12):
    for cell in col:
        print(cell.value, end=",")
    print()

print("--------修改内容-------------------")
sheet["B19"] = "Bible"

wb.save("名单.xlsx")

# print("--------删除-------------------")
# wb.remove(sheet)

print("--------设置格式-------------------")
#from openpyxl.styles import Font, colors, Alignment

myfont = Font(name="宋体", size=20, italic=True, color=colors.BLUE)

print(sheet["B19"].value)
sheet["B19"].font = myfont
#居中对齐
sheet["B19"].alignment = Alignment(vertical="center",horizontal="center")
# 行高
sheet.row_dimensions[19].height = 40
# 列宽
sheet.column_dimensions["B"].width = 30

wb.save("名单.xlsx")
